package com.efesco.finance.oughtReceive;

import com.bstek.dorado.data.*;
import com.bstek.dorado.data.db.SqlDataset;
import com.bstek.dorado.common.*;
import com.efesco.common.DBTools;
import com.efesco.common.Tools;
import com.efesco.system.User;

/**
 * queryArrear_arrearDatasetListener
 */
public class queryArrear_arrearDatasetListener extends AbstractDatasetListener {
  public boolean beforeLoadData(Dataset dataset)
     throws Exception {
    String fesc_user_id="",sql="",foreign_enterprise_id="";
  	
  	DoradoContext context = DoradoContext.getContext();
    Object obj = context.getAttribute(context.SESSION, "User");
    if (obj != null) {
    	User user = (User) obj;
    	int roleType = user.getRoleType();
    	if (roleType == 2)  {
    		fesc_user_id=user.getForEmployeeId();
    		foreign_enterprise_id=user.getForeignEnterpriseId();
    		sql = "select g.single_company_id,s.company_code,s.company_chinese_name,g.ought_month,g.ought_amount,g.gathering_amount, " +
					"a.modify_amount,g.payment_type,g.ought_amount+a.modify_amount-g.gathering_amount arrear_amount,fe.employee_name " +
					"from v_otreceive_gathering g,v_otreceive_adjust a,s_single_company s,s_company_businessman cbm,s_fe_employee fe " +
					"where g.ought_receive_id=a.ought_receive_id and g.single_company_id=s.single_company_id " + 
					"and s.foreign_enterprise_id="+foreign_enterprise_id+" and s.single_company_id=cbm.single_company_id  " +
					"and cbm.businessman_id=fe.employee_id and exists "+ DBTools.getRightSqlByBusinessman(fesc_user_id,"cbm") ;
					
    	}
    	else  {
    		sql = "select g.single_company_id,s.company_code,s.company_chinese_name,g.ought_month,g.ought_amount,g.gathering_amount, " +
				"a.modify_amount,g.payment_type,g.ought_amount+a.modify_amount-g.gathering_amount arrear_amount,fe.employee_name " +
				"from v_otreceive_gathering g,v_otreceive_adjust a,s_single_company s,s_company_businessman cbm,s_fe_employee fe " +
				"where g.ought_receive_id=a.ought_receive_id and g.single_company_id=s.single_company_id " + 
				"and s.single_company_id=cbm.single_company_id and cbm.business_id=1 and cbm.start_date<=sysdate and " + 
				"(cbm.end_date is null or cbm.end_date>=sysdate) and cbm.businessman_id=fe.employee_id  ";
    	}
    	if (!Tools.toStr(dataset.parameters().getString("singleCompanyId")).equals("")
    		&& !Tools.toStr(dataset.parameters().getString("singleCompanyId")).equals("null")	)  {
    		sql = sql + " and g.single_company_id = " + dataset.parameters().getString("singleCompanyId");
    	}
    	if (!Tools.toStr(dataset.parameters().getString("oughtMonth1")).equals("")
        	&& !Tools.toStr(dataset.parameters().getString("oughtMonth1")).equals("null")	)  {
        	sql = sql + " and g.ought_month >= '" + dataset.parameters().getString("oughtMonth1")+"'";
        }
    	if (!Tools.toStr(dataset.parameters().getString("oughtMonth2")).equals("")
            && !Tools.toStr(dataset.parameters().getString("oughtMonth2")).equals("null")	)  {
            sql = sql + " and g.ought_month <= '" + dataset.parameters().getString("oughtMonth2")+"'";
        }
    	if (!Tools.toStr(dataset.parameters().getString("amount1")).equals("")
    		&& !Tools.toStr(dataset.parameters().getString("amount1")).equals("null")	)  {
            sql = sql + " and g.ought_amount+a.modify_amount-g.gathering_amount >= " + dataset.parameters().getString("amount1");
        }
    	if (!Tools.toStr(dataset.parameters().getString("amount2")).equals("")
        	&& !Tools.toStr(dataset.parameters().getString("amount2")).equals("null")	)  {
            sql = sql + " and g.ought_amount+a.modify_amount-g.gathering_amount <= " + dataset.parameters().getString("amount2");
        }
    	if (!Tools.toStr(dataset.parameters().getString("businessmanId")).equals("")
        	&& !Tools.toStr(dataset.parameters().getString("businessmanId")).equals("null")	)  {
        	sql = sql + " and cbm.businessman_id = " + dataset.parameters().getString("businessmanId");
        }   
   		if (!Tools.toStr(dataset.parameters().getString("paymentType")).equals("")
   	        && !Tools.toStr(dataset.parameters().getString("paymentType")).equals("null"))  {
    		sql = sql + " and g.payment_type = " + dataset.parameters().getString("paymentType");
    	}
   
    	sql = sql + " order by s.single_company_id,g.ought_month ";
    	SqlDataset sqlDataset=(SqlDataset)dataset;
    	sqlDataset.setSql(sql);
    }
  	
    return true;
  }

}
